Use of TEXT and BLOB & Difference from VARCHAR
TEXT and BLOB are used for storing large amounts of data, but they differ from VARCHAR in storage behavior, indexing, and data type handling.
Used to store large text values such as descriptions, comments, and logs.
Stored off-page with only a pointer inside the table row.
Supports character set and collation.
Not fully indexable; only prefix indexing is allowed.
Used to store binary data such as images, videos, PDFs, and files.
Stored off-page similar to TEXT types.
Does not use character sets or collation.
Ideal for raw binary storage.
VARCHAR is stored inline with the table row, while TEXT and BLOB are stored off-page.
VARCHAR supports full indexing, but TEXT and BLOB require prefix indexing.
VARCHAR is for shorter, structured text; TEXT is for large text; BLOB is for binary data.
TEXT uses character encoding, while BLOB does not.